-- Copyright (c) Microsoft Corporation.  All rights reserved.

CREATE PROCEDURE [dbo].[CreateNewFolderIndex] @publicFolderUrl nvarchar(400), @indexName nvarchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	insert into dbo.FolderTree (FolderTreeUrl, FolderTreeName, DateCreated) 
		values (@publicFolderUrl, @indexName, getdate())

	return @@identity

END

GO

-- Gets the list of folder trees
CREATE PROCEDURE [dbo].[GetFolderTrees] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select FolderTreeID, FolderTreeName, FolderTreeUrl, DateCreated
	from FolderTree

END

GO

-- Returns the subfolders of the given folder. The result set
--	also contains the content index properties, if there are
CREATE PROCEDURE [dbo].[GetSubfolders]
	@folderId int,
	@folderTreeId int,
	@contentStatId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Select the direct subfolders under a given folder
	if (@folderId = 0)
		begin

			select 
				
				-- Folder properties
				fold.FolderID, fold.FolderTreeID, 
				fold.FolderName, fold.FolderKey, 
				fold.ItemCount,

				-- Content index properties
				indF.LastItemModification,

				-- Whether the folder is indexed
				case 
					when indF.FolderID is null then cast(0 as bit)
					else cast(1 as bit)
				end as Indexed,

				-- Owner and action plan properties
				fold.OwnerName, fold.OwnerEmail, 
				ap.ActionPlanName, ap.ActionPlanID,
				fold.Note

			from Folder fold
				
				left outer join IndexedFolder indF on 
					fold.FolderID = indF.FolderID and
					indF.ContentIndexID = @contentStatId
				
				left outer join ActionPlan ap on
					fold.ActionPlanID = ap.ActionPlanID

			where 
				ParentFolderID is null
				and FolderTreeID = @folderTreeId

		end
	else
		begin

			select 
				
				-- Folder properties
				fold.FolderID, fold.FolderTreeID, 
				fold.FolderName, fold.FolderKey, 
				fold.ItemCount,

				-- Content index properties
				indF.LastItemModification,

				-- Whether the folder is indexed
				case 
					when indF.FolderID is null then cast(0 as bit)
					else cast(1 as bit)
				end as Indexed,

				-- Owner and action plan properties
				fold.OwnerName, fold.OwnerEmail, 
				ap.ActionPlanName, ap.ActionPlanID,
				fold.Note

			from Folder fold

				left outer join IndexedFolder indF on 
					fold.FolderID = indF.FolderID and
					indF.ContentIndexID = @contentStatId

				left outer join ActionPlan ap on
					fold.ActionPlanID = ap.ActionPlanID

			where 
				ParentFolderID = @folderId

		end

END

GO

-- Returns the subfolders of the whole folder tree
CREATE PROCEDURE [dbo].[InsertIndexedFolderData]
	@folderId int,
	@contentIndexId int,
	@lastItemModification datetime
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert the index values
	insert into IndexedFolder (FolderID, ContentIndexID, LastItemModification)
	values (@folderId, @contentIndexId, @lastItemModification)

END

GO

-- Retrieves the number of folders for a given index tree
CREATE PROCEDURE [dbo].[GetNumberOfPublicFolders]
	@folderTreeId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @folderCount int

	-- Retrieve the number of folders for a given tree
	select @folderCount = count(FolderID) 
	from dbo.Folder
	where FolderTreeID = @folderTreeId

	return @folderCount

END

GO

-- Inserts one folder into the tree
CREATE PROCEDURE [dbo].[InsertFolderToFolderIndex]
	@folderIndexId int,
	@folderName nvarchar(120),
	@folderKey nvarchar(400),
	@itemCount int,
	@parentFolderId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert the new folder
	insert into Folder 
		(FolderTreeID, FolderName, FolderKey, Indexed, ItemCount, ParentFolderID, ActionPlanID)
	values 
		(@folderIndexId, @folderName, @folderKey, 0, @itemCount, @parentFolderId, 1)

	-- Select the last Id
	return @@identity

END

GO

-- Returns the subfolders of the whole folder tree
CREATE PROCEDURE [dbo].[GetAllFolders]
	@folderTreeId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select FolderID, FolderName, FolderKey, 
	'' as OwnerName, '' as OwnerEmail, 1 as ActionPlanID, '' as ActionPlanName, '' as Note
	from Folder
	where FolderTreeID = @folderTreeId

END

GO

-- Updates a folder's action plan and owner information
CREATE PROCEDURE [dbo].[UpdateFolder]
	@folderId int,
	@ownerName nvarchar(50),
	@ownerEmail nvarchar(100),
	@actionPlanId int,
	@note nvarchar(400)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	update Folder 
	set 
		OwnerName = @ownerName, 
		OwnerEmail = @ownerEmail, 
		ActionPlanID = @actionPlanId,
		Note = @note
	where FolderID = @folderId

END

GO

-- Creates a new content index catalog record
CREATE PROCEDURE [dbo].[CreateNewContentIndex]
	@folderTreeId int,
	@contentIndexName nvarchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	insert into ContentIndex
		(FolderTreeID, ContentIndexName, DateCreated) values
		(@folderTreeId, @contentIndexName, getdate())

	return @@identity

END

GO

-- Gets the list of content statistics for a given folder tree
CREATE PROCEDURE [dbo].[GetContentStatistics] 
	@folderTreeId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select ContentIndexID, ContentIndexName, DateCreated
	from ContentIndex
	where FolderTreeID = @folderTreeId

END

GO

-- Retrieves the action plans defined in the system
CREATE PROCEDURE [dbo].[GetActionPlans] 

AS
BEGIN

	select ActionPlanID, ActionPlanName from ActionPlan

END

GO

CREATE TABLE [dbo].[FolderTree](
	[FolderTreeID] [int] IDENTITY(1,1) NOT NULL,
	[FolderTreeName] [nvarchar](100) NULL,
	[DateCreated] [datetime] NULL CONSTRAINT [DF_FolderTree_DateCreated]  DEFAULT (getdate()),
	[FolderTreeUrl] [nvarchar](400) NULL,
 CONSTRAINT [PK_FolderTree] PRIMARY KEY CLUSTERED 
(
	[FolderTreeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[ContentIndex](
	[ContentIndexID] [int] IDENTITY(1,1) NOT NULL,
	[FolderTreeID] [int] NOT NULL,
	[ContentIndexName] [nvarchar](100) NOT NULL,
	[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_ContentIndex_DateCreated]  DEFAULT (getdate()),
 CONSTRAINT [PK_ContentIndex] PRIMARY KEY CLUSTERED 
(
	[ContentIndexID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ActionPlan](
	[ActionPlanID] [int] NOT NULL,
	[ActionPlanName] [nvarchar](40) NULL,
 CONSTRAINT [PK_ActionPlan] PRIMARY KEY CLUSTERED 
(
	[ActionPlanID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


insert into ActionPlan (ActionPlanID, ActionPlanName) values (1, 'Migrate To SharePoint')
insert into ActionPlan (ActionPlanID, ActionPlanName) values (2, 'Archived')
insert into ActionPlan (ActionPlanID, ActionPlanName) values (3, 'LOB Application')
insert into ActionPlan (ActionPlanID, ActionPlanName) values (4, 'Remains on Exchange')


CREATE TABLE [dbo].[Folder](
	[FolderID] [int] IDENTITY(1,1) NOT NULL,
	[FolderTreeID] [int] NOT NULL,
	[FolderName] [nvarchar](120) NOT NULL,
	[FolderKey] [nvarchar](400) NOT NULL,
	[Indexed] [bit] NOT NULL,
	[ItemCount] [int] NULL,
	[ParentFolderID] [int] NULL,
	[ActionPlanID] [int] NULL,
	[OwnerName] [nvarchar](50) NULL,
	[OwnerEmail] [nvarchar](100) NULL,
	[Note] [nvarchar](400) NULL,
 CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED 
(
	[FolderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[IndexedFolder](
	[IndexedFolderID] [int] IDENTITY(1,1) NOT NULL,
	[FolderID] [int] NOT NULL,
	[ContentIndexID] [int] NOT NULL,
	[LastItemModification] [datetime] NULL,
 CONSTRAINT [PK_IndexedFolder] PRIMARY KEY CLUSTERED 
(
	[IndexedFolderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]




ALTER TABLE [dbo].[Folder]  WITH CHECK ADD  CONSTRAINT [FK_Folder_ActionPlan] FOREIGN KEY([ActionPlanID])
REFERENCES [dbo].[ActionPlan] ([ActionPlanID])

ALTER TABLE [dbo].[Folder] CHECK CONSTRAINT [FK_Folder_ActionPlan]

/****** Object:  ForeignKey [FK_Folder_Folder]    Script Date: 04/27/2007 14:24:01 ******/
ALTER TABLE [dbo].[Folder]  WITH CHECK ADD  CONSTRAINT [FK_Folder_Folder] FOREIGN KEY([ParentFolderID])
REFERENCES [dbo].[Folder] ([FolderID])

ALTER TABLE [dbo].[Folder] CHECK CONSTRAINT [FK_Folder_Folder]

/****** Object:  ForeignKey [FK_Folder_FolderTree]    Script Date: 04/27/2007 14:24:01 ******/
ALTER TABLE [dbo].[Folder]  WITH CHECK ADD  CONSTRAINT [FK_Folder_FolderTree] FOREIGN KEY([FolderTreeID])
REFERENCES [dbo].[FolderTree] ([FolderTreeID])

ALTER TABLE [dbo].[Folder] CHECK CONSTRAINT [FK_Folder_FolderTree]

/****** Object:  ForeignKey [FK_IndexedFolder_ContentIndex]    Script Date: 04/27/2007 14:24:01 ******/
ALTER TABLE [dbo].[IndexedFolder]  WITH CHECK ADD  CONSTRAINT [FK_IndexedFolder_ContentIndex] FOREIGN KEY([ContentIndexID])
REFERENCES [dbo].[ContentIndex] ([ContentIndexID])

ALTER TABLE [dbo].[IndexedFolder] CHECK CONSTRAINT [FK_IndexedFolder_ContentIndex]

/****** Object:  ForeignKey [FK_IndexedFolder_Folder]    Script Date: 04/27/2007 14:24:01 ******/
ALTER TABLE [dbo].[IndexedFolder]  WITH CHECK ADD  CONSTRAINT [FK_IndexedFolder_Folder] FOREIGN KEY([FolderID])
REFERENCES [dbo].[Folder] ([FolderID])

ALTER TABLE [dbo].[IndexedFolder] CHECK CONSTRAINT [FK_IndexedFolder_Folder]